﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AcadPos.Entity;
using TrustX.Dados;
using System.Data;
using System.Data.SqlClient;

namespace AcadPos.Data
{
    public class DBTurma
    {
        #region Inserir
        public bool inserir(Turma turma, string usuario)
        {
            string SQL = @"INSERT INTO TB_TURMAS
                                  (  
                                    COD_TURMA,                                  
                                    ID_CURRICULO,
                                    ID_MODULO,                                    
                                    ID_POLO,                                   
                                    ID_PROFISSIONAL,
                                    LETRA_TURMA,
                                    QTD_PARCELAS,
                                    VALOR,
                                    DATA_INICIO,
                                    DATA_FIM,
                                    DATA_CADASTRO,
                                    SITUACAO
                                  )
                                VALUES
                                  (
                                    @COD_TURMA,
                                    @ID_CURRICULO,
                                    @ID_MODULO,
                                    @ID_POLO,
                                    @ID_PROFISSIONAL,
                                    @LETRA_TURMA,
                                    @QTD_PARCELAS,
                                    @VALOR,
                                    @DATA_INICIO,
                                    @DATA_FIM,
                                    GETDATE(),
                                    'A'
                                  )";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_CURRICULO", SqlDbType.Int, turma.IdCurriculo);
            db.AddParam("@ID_MODULO", SqlDbType.Int, turma.IdModulo);
            db.AddParam("@ID_POLO", SqlDbType.Int, turma.IdPolo);
            db.AddParam("@ID_PROFISSIONAL", SqlDbType.Int, turma.IdCoordenador);
            db.AddParam("@COD_TURMA", SqlDbType.VarChar, turma.CodTurma);
            db.AddParam("@LETRA_TURMA", SqlDbType.VarChar, turma.LetraTurma);
            db.AddParam("@QTD_PARCELAS", SqlDbType.Int, turma.QtdParcelas);
            db.AddParam("@VALOR", SqlDbType.Money, turma.Valor);
            db.AddParam("@DATA_INICIO", SqlDbType.DateTime, turma.DtInicio);
            db.AddParam("@DATA_FIM", SqlDbType.DateTime, turma.DtFim);
            //return db.ExecuteNonQuery();

            bool inseriu = db.ExecuteNonQuery();

            if (inseriu)
            {
                SQL = @"INSERT INTO TB_AUDITORIA
                      (
                        ACAO,
                        TABELA,
                        DESCRICAO,
                        USUARIO,
                        DATA                                    
                      )
                    VALUES
                      (                                     
                        'INSERT',
                        'TB_TURMAS',
                        ((SELECT CAST(MAX(ID_TURMA) AS VARCHAR) FROM TB_TURMAS)
                         + ' | ' + @COD_TURMA
                         + ' | ' + CAST(@ID_CURRICULO AS VARCHAR)
                         + ' | ' + CAST(@ID_MODULO AS VARCHAR)
                         + ' | ' + CAST(@ID_POLO AS VARCHAR)                        
                         + ' | ' + CAST(@ID_PROFISSIONAL AS VARCHAR)
                         + ' | ' + @LETRA_TURMA
                         + ' | ' + CAST(@QTD_PARCELAS AS VARCHAR)
                         + ' | ' + CAST(@VALOR AS VARCHAR)
                         + ' | ' + CONVERT(VARCHAR, @DATA_INICIO, 103)
                         + ' | ' + CONVERT(VARCHAR, @DATA_FIM, 103)
                         + ' | ' + 'A'),
                        UPPER(@USUARIO),
                        GETDATE()
                      )";

                db = new DataAccess(SQL);

                db.AddParam("@COD_TURMA", SqlDbType.VarChar, turma.CodTurma);
                db.AddParam("@ID_CURRICULO", SqlDbType.Int, turma.IdCurriculo);
                db.AddParam("@ID_MODULO", SqlDbType.Int, turma.IdModulo);
                db.AddParam("@ID_POLO", SqlDbType.Int, turma.IdPolo);
                db.AddParam("@ID_PROFISSIONAL", SqlDbType.Int, turma.IdCoordenador);
                db.AddParam("@LETRA_TURMA", SqlDbType.VarChar, turma.LetraTurma);
                db.AddParam("@QTD_PARCELAS", SqlDbType.Int, turma.QtdParcelas);
                db.AddParam("@VALOR", SqlDbType.Money, turma.Valor);
                db.AddParam("@DATA_INICIO", SqlDbType.DateTime, turma.DtInicio);
                db.AddParam("@DATA_FIM", SqlDbType.DateTime, turma.DtFim);
                db.AddParam("@USUARIO", SqlDbType.VarChar, usuario);
            }

            db.ExecuteNonQuery();
            return inseriu;
        }
        #endregion

        #region Alterar
        public bool alterar(Turma turma, string usuario)
        {
            string SQL = @"UPDATE TB_TURMAS
                                   set
                                   ID_CURRICULO = @ID_CURRICULO,
                                   ID_MODULO = @ID_MODULO,
                                   ID_POLO = @ID_POLO,
                                   ID_PROFISSIONAL = @ID_PROFISSIONAL,
                                   LETRA_TURMA = @LETRA_TURMA,
                                   QTD_PARCELAS = @QTD_PARCELAS,
                                   VALOR = @VALOR,
                                   DATA_INICIO = @DATA_INICIO,
                                   DATA_FIM = @DATA_FIM,
                                   COD_TURMA = @COD_TURMA
                                                          ";

            if (!String.IsNullOrEmpty(turma.MatriculaBloqLib))
            {
                if (turma.MatriculaBloqLib == "S")
                    SQL += @" ,LIBERAR_MATRICULA = @LIBERA_MATRICULA, DATA_LIBERAR_MATRICULA = GETDATE() ";
                else
                    SQL += @" ,LIBERAR_MATRICULA = @LIBERA_MATRICULA ";
            }
            if (!String.IsNullOrEmpty((turma.AulasBloqLib)))
            {
                if (turma.AulasBloqLib == "S")
                    SQL += @" ,LIBERAR_INICIO_AULA = @LIBERAR_INICIO_AULA, DATA_INICIO_AULA = GETDATE() ";
                else
                    SQL += @" ,LIBERAR_INICIO_AULA = @LIBERAR_INICIO_AULA ";
            }

            SQL += @"
                   WHERE  ID_TURMA = @ID_TURMA
                     
                 ";
            if (!String.IsNullOrEmpty((turma.AulasBloqLib)))
            {
                SQL += @"
                UPDATE TB_TURMAS
                            SET    DATA_INICIO     = DATA_INICIO_AULA
                            WHERE  ID_TURMA        = @ID_TURMA";
                        }



            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_CURRICULO", SqlDbType.Int, turma.IdCurriculo);
            db.AddParam("@ID_MODULO", SqlDbType.Int, turma.IdModulo);
            db.AddParam("@ID_POLO", SqlDbType.Int, turma.IdPolo);
            db.AddParam("@ID_PROFISSIONAL", SqlDbType.Int, turma.IdCoordenador);
            db.AddParam("@LETRA_TURMA", SqlDbType.VarChar, turma.LetraTurma);
            db.AddParam("@QTD_PARCELAS", SqlDbType.Int, turma.QtdParcelas);
            db.AddParam("@VALOR", SqlDbType.Money, turma.Valor);
            db.AddParam("@DATA_INICIO", SqlDbType.DateTime, turma.DtInicio);
            db.AddParam("@DATA_FIM", SqlDbType.DateTime, turma.DtFim);
            db.AddParam("@COD_TURMA", SqlDbType.VarChar, turma.CodTurma);

            db.AddParam("@ID_TURMA", SqlDbType.Int, turma.IdTurma);

            if (!String.IsNullOrEmpty(turma.MatriculaBloqLib))
            {
                db.AddParam("@LIBERA_MATRICULA", SqlDbType.VarChar, turma.MatriculaBloqLib);
            }
            if (!String.IsNullOrEmpty((turma.AulasBloqLib)))
            {
                db.AddParam("@LIBERAR_INICIO_AULA", SqlDbType.VarChar, turma.AulasBloqLib);
            }
            //return db.ExecuteNonQuery();

            bool atualizou = db.ExecuteNonQuery();

            if (atualizou)
            {
                SQL = @"INSERT INTO TB_AUDITORIA
                      (
                        ACAO,
                        TABELA,
                        DESCRICAO,
                        USUARIO,
                        DATA                                    
                      )
                    VALUES
                      (                                     
                        'UPDATE',
                        'TB_TURMAS',
                         (@ID_TURMA
                         + ' | ' + @COD_TURMA
                         + ' | ' + CAST(@ID_CURRICULO AS VARCHAR)
                         + ' | ' + CAST(@ID_MODULO AS VARCHAR)
                         + ' | ' + CAST(@ID_POLO AS VARCHAR)                        
                         + ' | ' + CAST(@ID_PROFISSIONAL AS VARCHAR)
                         + ' | ' + @LETRA_TURMA
                         + ' | ' + CAST(@QTD_PARCELAS AS VARCHAR)
                         + ' | ' + CAST(@VALOR AS VARCHAR)
                         + ' | ' + CONVERT(VARCHAR, @DATA_INICIO, 103)
                         + ' | ' + CONVERT(VARCHAR, @DATA_FIM, 103)
                         + ' | ' + 'A'),
                        UPPER(@USUARIO),
                        GETDATE()
                      )";

                db = new DataAccess(SQL);

                db.AddParam("@ID_TURMA", SqlDbType.VarChar, turma.IdTurma);
                db.AddParam("@COD_TURMA", SqlDbType.VarChar, turma.CodTurma);
                db.AddParam("@ID_CURRICULO", SqlDbType.Int, turma.IdCurriculo);
                db.AddParam("@ID_MODULO", SqlDbType.Int, turma.IdModulo);
                db.AddParam("@ID_POLO", SqlDbType.Int, turma.IdPolo);
                db.AddParam("@ID_PROFISSIONAL", SqlDbType.Int, turma.IdCoordenador);
                db.AddParam("@LETRA_TURMA", SqlDbType.VarChar, turma.LetraTurma);
                db.AddParam("@QTD_PARCELAS", SqlDbType.Int, turma.QtdParcelas);
                db.AddParam("@VALOR", SqlDbType.Money, turma.Valor);
                db.AddParam("@DATA_INICIO", SqlDbType.DateTime, turma.DtInicio);
                db.AddParam("@DATA_FIM", SqlDbType.DateTime, turma.DtFim);
                db.AddParam("@USUARIO", SqlDbType.VarChar, usuario);
            }

            db.ExecuteNonQuery();
            return atualizou;

        }
        #endregion

        #region Excluir
        public bool excluir(Turma turma, string usuario)
        {
            string SQL = @"DELETE 
                            FROM   TB_TURMAS
                            WHERE  ID_TURMA = @ID_TURMA";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_TURMA", SqlDbType.Int, turma.IdTurma);
            db.AddParam("@COD_TURMA", SqlDbType.VarChar, turma.CodTurma);
            db.AddParam("@ID_CURRICULO", SqlDbType.Int, turma.IdCurriculo);
            db.AddParam("@ID_MODULO", SqlDbType.Int, turma.IdModulo);
            db.AddParam("@ID_POLO", SqlDbType.Int, turma.IdPolo);
            db.AddParam("@ID_PROFISSIONAL", SqlDbType.Int, turma.IdCoordenador);
            db.AddParam("@LETRA_TURMA", SqlDbType.VarChar, turma.LetraTurma);
            db.AddParam("@QTD_PARCELAS", SqlDbType.Int, turma.QtdParcelas);
            db.AddParam("@VALOR", SqlDbType.Money, turma.Valor);
            db.AddParam("@DATA_INICIO", SqlDbType.DateTime, turma.DtInicio);
            db.AddParam("@DATA_FIM", SqlDbType.DateTime, turma.DtFim);
            //return db.ExecuteNonQuery();

            bool excluiu = db.ExecuteNonQuery();

            if (excluiu)
            {
                SQL = @"INSERT INTO TB_AUDITORIA
                      (
                        ACAO,
                        TABELA,
                        DESCRICAO,
                        USUARIO,
                        DATA                                    
                      )
                    VALUES
                      (                                     
                        'DELETE',
                        'TB_TURMAS',
                        (@ID_TURMA
                         + ' | ' + @COD_TURMA
                         + ' | ' + CAST(@ID_CURRICULO AS VARCHAR)
                         + ' | ' + CAST(@ID_MODULO AS VARCHAR)
                         + ' | ' + CAST(@ID_POLO AS VARCHAR)                        
                         + ' | ' + CAST(@ID_PROFISSIONAL AS VARCHAR)
                         + ' | ' + @LETRA_TURMA
                         + ' | ' + CAST(@QTD_PARCELAS AS VARCHAR)
                         + ' | ' + CAST(@VALOR AS VARCHAR)
                         + ' | ' + CONVERT(VARCHAR, @DATA_INICIO, 103)
                         + ' | ' + CONVERT(VARCHAR, @DATA_FIM, 103)
                         + ' | ' + 'A'),
                        UPPER(@USUARIO),
                        GETDATE()
                      )";

                db = new DataAccess(SQL);

                db.AddParam("@ID_TURMA", SqlDbType.VarChar, turma.IdTurma);
                db.AddParam("@COD_TURMA", SqlDbType.VarChar, turma.CodTurma);
                db.AddParam("@ID_CURRICULO", SqlDbType.Int, turma.IdCurriculo);
                db.AddParam("@ID_MODULO", SqlDbType.Int, turma.IdModulo);
                db.AddParam("@ID_POLO", SqlDbType.Int, turma.IdPolo);
                db.AddParam("@ID_PROFISSIONAL", SqlDbType.Int, turma.IdCoordenador);
                db.AddParam("@LETRA_TURMA", SqlDbType.VarChar, turma.LetraTurma);
                db.AddParam("@QTD_PARCELAS", SqlDbType.Int, turma.QtdParcelas);
                db.AddParam("@VALOR", SqlDbType.Money, turma.Valor);
                db.AddParam("@DATA_INICIO", SqlDbType.DateTime, turma.DtInicio);
                db.AddParam("@DATA_FIM", SqlDbType.DateTime, turma.DtFim);
                db.AddParam("@USUARIO", SqlDbType.VarChar, usuario);
            }

            db.ExecuteNonQuery();
            return excluiu;
        }

        #endregion

        #region Listar Turmas
        public DataTable listaTurmas(Turma turma)
        {
            string SQL = @"SELECT  TT.ID_TURMA,
                                   tt.ID_CURRICULO,
                                   tc.CURRICULO,
                                   tm.ID_MODULO, tt.ID_POLO,
                                   tm.DESCRICAO as DESCRICAO_MODULO,
                                   tt.COD_TURMA,
                                   tt.QTD_PARCELAS,
                                   tt.VALOR, tt.SITUACAO,
                                   CONVERT(VARCHAR,tt.DATA_INICIO,103) AS DATA_INICIO,
                                   CONVERT(VARCHAR,tt.DATA_FIM,103) AS DATA_FIM,
                                   tp.ID_PROFISSIONAL,
                                   tp.NOME AS NOME_PROFISSIONAL,
                                      (
                                           SELECT COUNT(*) AS QTD_DISC
                                           FROM   TB_MATRIZ_CURRICULAR tmc
                                                  INNER JOIN TB_MODULO_CURRICULO tmc2
                                                       ON  tmc2.ID_MODULO_CURRICULO = tmc.ID_MODULO_CURRICULO
                                           WHERE  tmc2.ID_CURRICULO = tc.ID_CURRICULO
                                                  AND tmc2.ID_MODULO = tm.ID_MODULO
                                       )             AS QTD_DISC, LIBERAR_MATRICULA, LIBERAR_INICIO_AULA
                            FROM   TB_TURMAS tt
                                   INNER JOIN TB_MODULO tm
                                        ON  tm.ID_MODULO = tt.ID_MODULO
                                   INNER JOIN TB_CURRICULO tc
                                        ON  tc.ID_CURRICULO = tt.ID_CURRICULO
                                   INNER JOIN TB_PROFISSIONAL tp
                                        ON  tp.ID_PROFISSIONAL = tt.ID_PROFISSIONAL
                            WHERE  tt.ID_CURRICULO = @ID_CURRICULO
                                   AND tt.ID_POLO = @ID_POLO
                                   AND GETDATE() <= tt.DATA_FIM	
       	                            ";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_CURRICULO", SqlDbType.Int, turma.IdCurriculo);
            db.AddParam("@ID_POLO", SqlDbType.Int, turma.IdPolo);
            return db.GetDataTable();
        }
        #endregion

        #region Listar Turmas
        public DataTable obterRelatorioTurmas(Turma turma)
        {
            string SQL = @"SELECT TT.ID_TURMA,
                                   tt.ID_CURRICULO,
                                   tc.CURRICULO,
                                   tm.ID_MODULO,
                                   tt.ID_POLO,
                                   tm.DESCRICAO   AS DESCRICAO_MODULO,
                                   tt.COD_TURMA,
                                   tt.QTD_PARCELAS,
                                   tt.VALOR,
                                   CONVERT(VARCHAR, tt.DATA_INICIO, 103) AS DATA_INICIO,
                                   CONVERT(VARCHAR, tt.DATA_FIM, 103) AS DATA_FIM,
                                   tp.ID_PROFISSIONAL,
                                   tp.NOME        AS NOME_PROFISSIONAL,
                                   (
                                       SELECT COUNT(*) AS QTD_DISC
                                       FROM   TB_MATRIZ_CURRICULAR tmc
                                              INNER JOIN TB_MODULO_CURRICULO tmc2
                                                   ON  tmc2.ID_MODULO_CURRICULO = tmc.ID_MODULO_CURRICULO
                                       WHERE  tmc2.ID_CURRICULO = tc.ID_CURRICULO
                                              AND tmc2.ID_MODULO = tm.ID_MODULO
                                   )              AS QTD_DISC,
                                   tm.DESCRICAO   AS MODULO,
                                   tc2.DESCRICAO  AS NOME_CURSO,
                                   tac.ID_AREA_CONHECIMENTO,
                                   tac.DESCRICAO  AS NOME_AREA_CONHECIMENTO,
                                   tp2.DESCRICAO  AS NOME_POLO,
                                   tp2.ID_ESTADO,
                                   te.ESTADO,
                                   CASE
                                   WHEN GETDATE() BETWEEN tt.DATA_INICIO AND tt.DATA_FIM THEN 'Em Andamento'
                                   WHEN GETDATE() < tt.DATA_INICIO THEN 'Não Iniciado'
                                   WHEN GETDATE() > tt.DATA_FIM THEN 'Concluído'
                                   END AS SITUACAO,
                                   dbo.CalcQtdConcluidosTurma(tt.ID_TURMA) AS QTD_CONCLUIDOS,
                                   dbo.CalcQtdRegularesTurma(tt.ID_TURMA) AS QTD_REGULARES
                            FROM   TB_TURMAS tt
                                   INNER JOIN TB_MODULO tm
                                        ON  tm.ID_MODULO = tt.ID_MODULO
                                   INNER JOIN TB_CURRICULO tc
                                        ON  tc.ID_CURRICULO = tt.ID_CURRICULO
                                   INNER JOIN TB_PROFISSIONAL tp
                                        ON  tp.ID_PROFISSIONAL = tt.ID_PROFISSIONAL
                                   INNER JOIN TB_CURSO tc2
                                        ON  tc2.ID_CURSO = tc.ID_CURSO
                                   INNER JOIN TB_AREA_CONHECIMENTO tac
                                        ON  tac.ID_AREA_CONHECIMENTO = tc2.ID_AREA_CONHECIMENTO
                                   INNER JOIN TB_POLO tp2
                                        ON  tp2.ID_POLO = tt.ID_POLO
                                   INNER JOIN TB_ESTADO te
                                        ON  te.ID_ESTADO = tp2.ID_ESTADO where TC2.ID_CURSO = @ID_CURSO
                            ";
            if (turma.IdCurriculo > 0)
            {
                SQL += " AND tt.ID_CURRICULO = @ID_CURRICULO ";
            }
            if (turma.IdPolo > 0)
            {
                SQL += " AND tt.ID_POLO = @ID_POLO ";
            }
            if (turma.Situacao == "N")
            {
                SQL += " AND GETDATE() < tt.DATA_INICIO ";
            }
            if (turma.Situacao == "E")
            {
                SQL += " AND GETDATE() BETWEEN tt.DATA_INICIO AND tt.DATA_FIM	 ";
            }
            if (turma.Situacao == "C")
            {
                SQL += " AND GETDATE() > tt.DATA_FIM ";
            }

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_CURRICULO", SqlDbType.Int, turma.IdCurriculo);
            db.AddParam("@ID_POLO", SqlDbType.Int, turma.IdPolo);
            db.AddParam("@ID_CURSO", SqlDbType.Int, turma.IdCurso);
            return db.GetDataTable();
        }
        #endregion

        #region Listar Coordenador Por Descrição
        public DataTable listaCoordenadorDescricao(string descricaoCoord)
        {
            string SQL = @"SELECT   TP.ID_PROFISSIONAL,
                                   TP.NOME,TP.SITUACAO
                            FROM   TB_PROFISSIONAL AS tp
                            WHERE  tp.NOME LIKE @DESCRICAO + '%'";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@DESCRICAO", SqlDbType.VarChar, descricaoCoord);
            return db.GetDataTable();
        }
        #endregion

        #region Listar Turmas Objeto
        public Turma listaTurmasObj(Turma turma)
        {
            string SQL = @"SELECT  TT.ID_TURMA, TT.LETRA_TURMA, 
                                   tt.ID_CURRICULO,
                                   tc.CURRICULO,
                                   tm.ID_MODULO,
                                   tm.DESCRICAO AS DESCRICAO_MODULO,
                                   tt.COD_TURMA,
                                   tt.QTD_PARCELAS,
                                   tt.VALOR,
                                   tt.DATA_INICIO,
                                   tt.DATA_FIM,
                                   tp.ID_PROFISSIONAL,
                                   tp.NOME AS NOME_PROFISSIONAL,
                                   tp2.ID_ESTADO,
                                   tc2.ID_CURSO,
                                   tac.ID_AREA_CONHECIMENTO, tt.ID_POLO,LIBERAR_MATRICULA,LIBERAR_INICIO_AULA
                            FROM   TB_TURMAS tt
                                   INNER JOIN TB_MODULO tm
                                        ON  tm.ID_MODULO = tt.ID_MODULO
                                   INNER JOIN TB_CURRICULO tc
                                        ON  tc.ID_CURRICULO = tt.ID_CURRICULO
                                   INNER JOIN TB_PROFISSIONAL tp
                                        ON  tp.ID_PROFISSIONAL = tt.ID_PROFISSIONAL
                                   INNER JOIN TB_POLO               AS tp2
                                        ON  tp2.ID_POLO = tt.ID_POLO
                                   INNER JOIN TB_CURSO              AS tc2
                                        ON  tc2.ID_CURSO = tc.ID_CURSO
                                   INNER JOIN TB_AREA_CONHECIMENTO  AS tac
                                        ON  tac.ID_AREA_CONHECIMENTO = tc2.ID_AREA_CONHECIMENTO
                            WHERE  TT.ID_TURMA = @ID_TURMA	   ";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_TURMA", SqlDbType.Int, turma.IdTurma);
            try
            {
                db.AbrirConexao();
                SqlDataReader dr = db.ExecuteReader();
                if (dr.Read())
                {
                    turma.IdTurma = Convert.ToInt32(dr["ID_TURMA"].ToString());
                    turma.IdAreaConhecimento = Convert.ToInt32(dr["ID_AREA_CONHECIMENTO"].ToString());
                    turma.IdCurso = Convert.ToInt32(dr["ID_CURSO"].ToString());
                    turma.CodTurma = dr["COD_TURMA"].ToString();
                    turma.IdCurriculo = Convert.ToInt32(dr["ID_CURRICULO"].ToString());
                    turma.IdModulo = Convert.ToInt32(dr["ID_MODULO"].ToString());
                    turma.IdPolo = Convert.ToInt32(dr["ID_POLO"].ToString());
                    turma.LetraTurma = dr["LETRA_TURMA"].ToString();
                    turma.QtdParcelas = Convert.ToInt32(dr["QTD_PARCELAS"].ToString());
                    turma.Valor = Convert.ToDecimal(dr["VALOR"].ToString());
                    turma.DtInicio = Convert.ToDateTime(dr["DATA_INICIO"].ToString());
                    turma.DtFim = Convert.ToDateTime(dr["DATA_FIM"].ToString());
                    turma.IdEstado = Convert.ToInt32(dr["ID_ESTADO"].ToString());
                    turma.IdCoordenador = Convert.ToInt32(dr["ID_PROFISSIONAL"].ToString());
                    turma.MatriculaBloqLib = dr["LIBERAR_MATRICULA"].ToString();
                    turma.AulasBloqLib = dr["LIBERAR_INICIO_AULA"].ToString();
                }
                return turma;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                db.FecharConexao();
            }
        }
        #endregion

        #region valida turma quanto a ano, letra, curso,polo e modulo
        public bool validaTurma(Turma turma)
        {
            string SQL = @"SELECT  TT.ID_TURMA
                            FROM   TB_TURMAS                AS tt
                                   INNER JOIN TB_CURRICULO  AS tc
                                        ON  tc.ID_CURRICULO = tt.ID_CURRICULO
                            WHERE  tc.ID_CURSO = @ID_CURSO
                                   AND tt.LETRA_TURMA = @LETRA_TURMA
                                   AND YEAR(TT.DATA_INICIO) = @ANO
                                   AND tt.ID_POLO = @ID_POLO
                                   AND TT.ID_MODULO = @ID_MODULO";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_CURSO", SqlDbType.Int, turma.IdCurso);
            db.AddParam("@LETRA_TURMA", SqlDbType.VarChar, turma.LetraTurma);
            db.AddParam("@ANO", SqlDbType.Int, Convert.ToInt32(turma.DtInicio.Year));
            db.AddParam("@ID_POLO", SqlDbType.Int, turma.IdPolo);
            db.AddParam("@ID_MODULO", SqlDbType.Int, turma.IdModulo);
            try
            {
                db.AbrirConexao();
                SqlDataReader dr = db.ExecuteReader();
                if (dr.Read())
                {
                    return true;
                }
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                db.FecharConexao();
            }
        }
        #endregion

        #region Listar Cursos por Codigo
        public DataTable listarCursosPorCodigo(Curso curso)
        {
            string SQL = @"SELECT  tc.ID_CURSO,
                                   tc.ID_AREA_CONHECIMENTO,
                                   tc.DESCRICAO,
                                   tc.DATA_RECONHECIMENTO,
                                   tc.DESCRICAO_ABREVIADA,
                                   tc.RECONHECIMENTO,
                                   tc.SITUACAO
                            FROM   TB_CURSO tc
                                   INNER JOIN TB_AREA_CONHECIMENTO tac
                                        ON  tac.ID_AREA_CONHECIMENTO = tc.ID_AREA_CONHECIMENTO
                            WHERE  tc.ID_CURSO = @ID_CURSO
                                   AND tc.ID_AREA_CONHECIMENTO = @ID_AREA_CONHECIMENTO";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_AREA_CONHECIMENTO", SqlDbType.Int, curso.IdAreaConhecimento);
            db.AddParam("@ID_CURSO", SqlDbType.VarChar, curso.IdCurso);
            return db.GetDataTable();
        }
        #endregion

        #region Listar Profissional por Codigo
        public DataTable listarProfissionalPorCodigo(Turma turma)
        {
            string SQL = @"SELECT TP.ID_PROFISSIONAL, TP.NOME, TP.SITUACAO
                                FROM   TB_PROFISSIONAL AS tp
                                WHERE  tp.ID_PROFISSIONAL = @ID_PROFISSIONAL";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_PROFISSIONAL", SqlDbType.Int, turma.IdCoordenador);
            return db.GetDataTable();
        }
        #endregion

        #region Listar Notas e Frequências por Turma e Disciplina
        public DataTable getNotasFrequencias(string pTurma, int pCodDisc)
        {
            string SQL = @"SELECT ID_MATRICULA_DISCIPLINA,
                               tm.MATRICULA,
                               UPPER(ta.NOME) AS NOME,
                               tmd.NOTA1,
                               tmd.NOTA2,
                               ISNULL(tmd.FALTA1,0) AS FALTA1,
                               ISNULL(tmd.FALTA2,0) AS FALTA2,
                               ISNULL(tmd.FALTA3,0) AS FALTA3,
                               ISNULL(tmd.FALTA4,0) AS FALTA4,
                               tmd.SITUACAO      
                        FROM   TB_MATRICULA_DISCIPLINA tmd
                               JOIN TB_MATRICULA tm
                                    ON  tm.ID_MATRICULA = tmd.ID_MATRICULA
                               JOIN TB_ALUNO ta
                                    ON  ta.MATRICULA = tm.MATRICULA
                               JOIN TB_TURMAS tt
                                    ON  tt.ID_TURMA = tm.ID_TURMA
                        WHERE  tt.COD_TURMA = @TURMA
                               AND tmd.ID_DISCIPLINA = @COD_DISCIPLINA
                        ORDER BY
                               ta.NOME";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@TURMA", SqlDbType.VarChar, pTurma);
            db.AddParam("@COD_DISCIPLINA", SqlDbType.Int, pCodDisc);
            return db.GetDataTable();
        }
        #endregion

        #region Lançar Notas do Aluno
        public bool lancarNotasAluno(int pID, float pNota1, float pNota2)
        {
            string SQL = @"UPDATE TB_MATRICULA_DISCIPLINA
                            SET  
                                   NOTA1 = @NOTA1,
                                   NOTA2 = @NOTA2
                            WHERE  ID_MATRICULA_DISCIPLINA = @ID_MATRICULA_DISCIPLINA";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_MATRICULA_DISCIPLINA", SqlDbType.Int, pID);
            db.AddParam("@NOTA1", SqlDbType.Float, pNota1);
            db.AddParam("@NOTA2", SqlDbType.Float, pNota2);
            return db.ExecuteNonQuery();
        }
        #endregion

        #region validaLiberaMatricula
        public bool validaLiberaMatricula(Turma turma)
        {
            string SQL = @"exec VALIDA_LIBERACAO_FINANC_MAT
                                     @ID_TURMA";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_TURMA", SqlDbType.Int, turma.IdTurma);
            DataTable dt = db.GetDataTable();
            return Convert.ToBoolean(dt.Rows[0][0].ToString());
        }
        #endregion

        #region Valida Libera Aulas
        public bool validaLiberaAulas(Turma turma)
        {
            string SQL = @"SELECT   COUNT(*)
                                    FROM   TB_FINANCEIRO tf
                                    WHERE  tf.ID_TURMA = @ID_TURMA
                                           AND tf.PARCELA = 1";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_TURMA", SqlDbType.Int, turma.IdTurma);
            DataTable dt = db.GetDataTable();
            int qtd = Convert.ToInt32(dt.Rows[0][0].ToString());
            if (qtd >= 35)
                return true;
            else
                return false;
        }
        #endregion

        public DataTable getDadosTurma(int IdTurma)
        {
            string SQL = @"SELECT
                                ID_TURMA, 
                                COD_TURMA, 
                                ID_CURRICULO, 
                                ID_MODULO, 
                                ID_POLO, 
                                ID_PROFISSIONAL, 
                                LETRA_TURMA,  
                                QTD_PARCELAS, 
                                VALOR, 
                                DATA_INICIO, 
                                DATA_FIM, 
                                DATA_CADASTRO, 
                                SITUACAO, 
                                LIBERAR_MATRICULA
                                
                                FROM TB_TURMAS";

            DataAccess db = new DataAccess(SQL);

            return db.GetDataTable();
        }
    }
}
